package com.example.anydo.util.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;

import com.example.anydo.bean.OperationBean;
import com.example.anydo.bean.SubTaskBean;
import com.example.anydo.bean.SynchronizeBodyBean;
import com.example.anydo.bean.TaskBean;
import com.example.anydo.bean.UserBean;
import com.example.anydo.util.database.table_info.OperationTable;
import com.example.anydo.util.database.table_info.SubTaskTable;
import com.example.anydo.util.database.table_info.TaskTable;
import com.example.anydo.util.database.table_info.UserTable;

import java.util.ArrayList;
import java.util.List;

public class DatabaseHelperOldVersion extends SQLiteOpenHelper {
    private static final int version = 4;
    private static final String databaseName = "AnyDoCaches"+version+".db";
    private String TAG;

    // 创建用户表
    private static final String createUserTableSQL =
            "Create Table " + UserTable.tableName + " (" +
                    "id                     Integer Primary Key AutoIncrement Not Null, " +
                    "name                   text Not Null, " +
                    "create_time            text Not Null, " +
                    "history_task_num       int Not Null Default 0, " +
                    "history_done_task_num  int Not Null Default 0, " +
                    "today_task_num         int Not Null Default 0, " +
                    "today_done_task_num    int Not Null Default 0, " +
                    "wait_task_num          int Not Null Default 0);";
    // 创建任务表
    private static final String createTaskTableSQL =
            "Create Table " + TaskTable.tableName + " (" +
                    "id            Integer Primary Key AutoIncrement Not Null, " +
                    "user_id       Integer Not Null, " +
                    "title         text Not Null, " +
                    "description   text Not Null, " +
                    "date          text Not Null, " +
                    "time          text Not Null, " +
                    "rate          text Not Null, " +
                    "if_done       text Not Null, " +
                    "Foreign Key(user_id) References " + UserTable.tableName + "(id));";
    // 创建子任务表
    private static final String createSubTaskTableSQL =
            "Create Table " + SubTaskTable.tableName + " (" +
                    "id         Integer Primary Key AutoIncrement Not Null, " +
                    "task_id    Integer Not Null, " +
                    "title      text Not Null, " +
                    "if_done    text Not Null, " +
                    "Foreign Key(task_id) References " + TaskTable.tableName + "(id));";
    // 创建操作表
    private static final String createOperationTableSQL =
            "Create Table " + OperationTable.TABLE_NAME + " (" +
                    "id         Integer Primary Key AutoIncrement Not Null, " +
                    "user_id     Integer Not Null,"+
                    "type       Integer Not Null, " +
                    "type_id    text Not Null, " +
                    "crud       text Not Null);";


    // 基于用户id查询用户名
    private static final String getUserNameByIdSQL =
            "Select " + UserTable.name + " From " + UserTable.tableName + " " +
                    "Where " + UserTable.id + " = ?;";
    // 基于用户id查询用户表中的所有数据
    private static final String getUserDataSQL =
            "Select " + UserTable.name + ", " + UserTable.createTime + ", " +
                    UserTable.historyTaskNum + ", " + UserTable.historyDoneTaskNum + ", " +
                    UserTable.todayTaskNum + ", " + UserTable.todayDoneTaskNum + ", " +
                    UserTable.waitTaskNum + " " +
                    "From " + UserTable.tableName + " " +
                    "Where " + UserTable.id + " = ?;";

    // 查询所有的待办任务，待办任务由 date == "" 确定
    private static final String getWaitingTaskSQL =
            "Select " + TaskTable.id + ", " + TaskTable.title + ", " +
                    TaskTable.description + ", " + TaskTable.time + ", " +
                    TaskTable.rate + ", " + TaskTable.ifDone + " " +
                    "From " + TaskTable.tableName + " " +
                    "Where " + TaskTable.userId + " = ? and " + TaskTable.date + " = '';";

    // 用户表中能被修改数值的所有列的列名
    private static final ArrayList<String> userCountColumnsName = new ArrayList<>();

    public DatabaseHelperOldVersion(@Nullable Context context) {
        super(context, databaseName, null, version);
        TAG = getClass().getSimpleName();

        userCountColumnsName.add(UserTable.historyTaskNum);
        userCountColumnsName.add(UserTable.historyDoneTaskNum);
        userCountColumnsName.add(UserTable.todayTaskNum);
        userCountColumnsName.add(UserTable.todayDoneTaskNum);
        userCountColumnsName.add(UserTable.waitTaskNum);
    }

    // 输出错误日志
    private void logError(String message) {
        Log.e(TAG, message);
    }

    // 输出信息日志
    private void logInfo(String message) {
        logError(message);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(createUserTableSQL);
        logInfo("创建用户表，执行的SQL语句是：" + createUserTableSQL);
        db.execSQL(createTaskTableSQL);
        logInfo("创建任务表，执行的SQL语句是：" + createTaskTableSQL);
        db.execSQL(createSubTaskTableSQL);
        logInfo("创建子任务表，执行的SQL语句是：" + createSubTaskTableSQL);

        db.execSQL(createOperationTableSQL);
        logInfo("创建操作表，执行的SQL语句是：" + createOperationTableSQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    /**
     * SynchronizeBodyBean，新建一个用户缓存记录，
     */
    public boolean addUserCache(SynchronizeBodyBean bodyBean) {
        // user表
        ContentValues userValues = new ContentValues();
        UserBean.DataBean user = bodyBean.getData().getUser();
        userValues.put(UserTable.id, user.getId());
        userValues.put(UserTable.name, user.getName());
        userValues.put(UserTable.createTime, user.getCreateTime());
        userValues.put(UserTable.historyTaskNum, user.getHistoryTaskNum());
        userValues.put(UserTable.historyDoneTaskNum, user.getHistoryDoneTaskNum());
        userValues.put(UserTable.todayTaskNum, user.getTodayTaskNum());
        userValues.put(UserTable.todayDoneTaskNum, user.getTodayDoneTaskNum());
        userValues.put(UserTable.waitTaskNum, user.getWaitTaskNum());

        // task表
        List<ContentValues> taskValues = new ArrayList<>();
        List<TaskBean.DataBean> tasks = bodyBean.getData().getTasks();
        for (TaskBean.DataBean t: tasks) {
            ContentValues taskValue = new ContentValues();
            taskValue.put(TaskTable.id,t.getId());
            taskValue.put(TaskTable.userId,t.getUserId());
            taskValue.put(TaskTable.title,t.getTitle());
            taskValue.put(TaskTable.description,t.getDescription());
            taskValue.put(TaskTable.date,t.getDate());
            taskValue.put(TaskTable.time,t.getTime());
            taskValue.put(TaskTable.rate,t.getRate());
            taskValue.put(TaskTable.ifDone,t.getIfDone());
            taskValues.add(taskValue);
        }

        List<ContentValues> subTaskValues = new ArrayList<>();
        List<SubTaskBean.DataBean> subTasks = bodyBean.getData().getSubTasks();
        for (SubTaskBean.DataBean sub: subTasks) {
            ContentValues subValue = new ContentValues();
            subValue.put(SubTaskTable.id,sub.getId());
            subValue.put(SubTaskTable.taskId,sub.getTaskId());
            subValue.put(SubTaskTable.title,sub.getTitle());
            subValue.put(SubTaskTable.ifDone,sub.getIfDone());
            subTaskValues.add(subValue);
        }

        boolean success = false;
        SQLiteDatabase database = getWritableDatabase();
        try {
            database.beginTransaction();

            database.insert(UserTable.tableName, null, userValues);
            for (ContentValues values: taskValues) {
                database.insert(TaskTable.tableName,null,values);
            }

            for (ContentValues values: subTaskValues) {
                database.insert(SubTaskTable.tableName,null,values);
            }

            logError("\n\n===================downloadData=============================");
            logError(bodyBean.toString());
            logError("===================downloadData=============================\n\n");


            database.setTransactionSuccessful();
            success = true;
        }catch (Exception e){
            e.printStackTrace();
            success = false;
        }finally {
            database.endTransaction();
            return success;
        }
    }


    /**
     * 获取用户名
     */
    public String getUserNameById(Integer userId) {
        String userName = "";
        // 执行查询语句并尝试读取用户名
        SQLiteDatabase database = getReadableDatabase();
        // TODO: 现在这里查询条件并不是一个字符串，但是只提供了一个字符串数组形式的接口，等测试之后确定一下
        Cursor cursor = database.rawQuery(getUserNameByIdSQL, new String[]{String.valueOf(userId)});
        if (cursor.moveToFirst()) {
            userName = cursor.getString(0);
        }

        // 关闭游标和数据库
        cursor.close();
        database.close();

        logInfo("基于用户id获取用户姓名，获取的姓名为：" + userName);

        return userName;
    }

    /**
     * 获取用户 Userbean
     */
    public UserBean getUserData(Integer userId) {
        UserBean result = null;

        // 打开数据库并查询
        SQLiteDatabase database = getReadableDatabase();
        Cursor cursor = database.rawQuery(getUserDataSQL, new String[]{String.valueOf(userId)});
        if (cursor.moveToFirst()) {
            result = new UserBean();
            UserBean.DataBean dataBean = new UserBean.DataBean();
            dataBean.setId(userId);
            dataBean.setName(cursor.getString(0));
            dataBean.setCreateTime(cursor.getString(1));
            dataBean.setHistoryTaskNum(cursor.getInt(2));
            dataBean.setHistoryDoneTaskNum(cursor.getInt(3));
            dataBean.setTodayTaskNum(cursor.getInt(4));
            dataBean.setTodayDoneTaskNum(cursor.getInt(5));
            dataBean.setWaitTaskNum(cursor.getInt(6));
            result.setData(dataBean);
        }

        // 关闭游标和数据库
        cursor.close();
        database.close();

        logInfo("获取id为" + userId + "的用户的相关数据");

        return result;
    }

    /**
     * 新建任务
     */
    public boolean saveTask(Integer userId, TaskBean taskBean, boolean add) {
        SQLiteDatabase database = getWritableDatabase();

        TaskBean.DataBean dataBean = taskBean.getData();
        ContentValues contentValues = new ContentValues();

        long newId = -1;
        // 编辑任务
        if(!add){
            newId = dataBean.getId();
            contentValues.put(TaskTable.id, dataBean.getId());
        }

        contentValues.put(TaskTable.userId, userId);
        contentValues.put(TaskTable.title, dataBean.getTitle());
        contentValues.put(TaskTable.description, dataBean.getDescription());
        contentValues.put(TaskTable.date, dataBean.getDate());
        contentValues.put(TaskTable.time, dataBean.getTime());
        contentValues.put(TaskTable.rate, dataBean.getRate());
        contentValues.put(TaskTable.ifDone, dataBean.getIfDone());

        ArrayList<SubTaskBean.DataBean> subTasks = dataBean.getSubTasks();
        ArrayList<ContentValues> subValues = new ArrayList<>();
        //ArrayList<Integer> deletes = new ArrayList<>();
        if(subTasks!= null){
            for(SubTaskBean.DataBean data : subTasks){

                // 记录已有任务，即将删除
                //deletes.add(data.getId());
                ContentValues cv = new ContentValues();
                cv.put(SubTaskTable.title,data.getTitle());
                cv.put(SubTaskTable.ifDone,data.getIfDone());
                subValues.add(cv);
            }
        }

        boolean success ;
        try {
            database.beginTransaction();
            if(add){
                newId = database.insert(TaskTable.tableName, null, contentValues);
                logError("add new task successful , id = " + newId);
            }else {
                database.update(
                TaskTable.tableName,
                contentValues,
                TaskTable.id + " = ? and " + TaskTable.userId + " = ?",
                new String[]{String.valueOf(newId), String.valueOf(userId)});
                logError("update task successful , id = " + newId);
            }

            /// 删除子任务（必须先删除再添加）
            if(!add){
                deleteSubTaskOf(dataBean.getId());
                // 删除所有子任务操作记录
                recordOperation(database,userId,OperationTable.SUB_TASK_TYPE, dataBean.getId(), OperationTable.CRUD_D);
            }


            for (ContentValues values:subValues) {
                values.put(SubTaskTable.taskId, newId);
                // 添加子任务
                long subId = database.insert(SubTaskTable.tableName, null, values);
                // 记录添加子任务
                recordOperation(database,userId,OperationTable.SUB_TASK_TYPE, subId, OperationTable.CRUD_C);
            }

            recordOperation(database,userId,OperationTable.TASK_TYPE, newId, OperationTable.CRUD_C);


            database.setTransactionSuccessful();
            success = true;
        }catch (Exception e){
            e.printStackTrace();
            success = false;
        }finally {
            database.endTransaction();
        }
        logInfo("添加新的任务，新增任务的自增id：" + newId + taskBean.toString());
        return success;
    }


    // 查询指定用户在指定日期的所有任务
    private static final String getTaskOfDaySQL =
            "Select " + TaskTable.id + ", " + TaskTable.title + ", " +
                    TaskTable.description + ", " + TaskTable.time + ", " +
                    TaskTable.rate + ", " + TaskTable.ifDone + " " +
                    "From " + TaskTable.tableName + " " +
                    "Where " + TaskTable.userId + " = ? and " + TaskTable.date + " = ?;";
    /**
     * 获取userId指定的用户在date日期的所有任务（内部包含子任务）
     */
    public ArrayList<TaskBean> getTasksOfDay(Integer userId, String date) {
        ArrayList<TaskBean> result = null;

        SQLiteDatabase database = getReadableDatabase();
        Cursor cursor = database.rawQuery(getTaskOfDaySQL, new String[]{String.valueOf(userId), date});
        if (cursor.getCount() > 0) {
            result = new ArrayList<>();
            TaskBean temp;
            TaskBean.DataBean tempDataBean;
            while (cursor.moveToNext()) {
                temp = new TaskBean();
                tempDataBean = new TaskBean.DataBean();
                tempDataBean.setDate(date);

                int taskId = cursor.getInt(0);
                tempDataBean.setId(taskId);
                tempDataBean.setTitle(cursor.getString(1));
                tempDataBean.setDescription(cursor.getString(2));
                tempDataBean.setTime(cursor.getString(3));
                tempDataBean.setRate(cursor.getString(4));
                tempDataBean.setIfDone(cursor.getString(5));

                // 填充子任务
                tempDataBean.setSubTasks(getSubTasks(taskId));

                temp.setData(tempDataBean);

                result.add(temp);
            }
        }
        logError("获取用户" + userId + "在" + date + "的所有任务: 共" + cursor.getCount() + "个");
        // 关闭游标和数据库
        cursor.close();
        database.close();
        return result;
    }


    // 查询指定用户在指定日期的所有任务
    private static final String getSubTaskOfTaskSQL =
            "Select " + SubTaskTable.id + ", " + SubTaskTable.title + ", " +
                    SubTaskTable.taskId + ", " + SubTaskTable.ifDone + " " +
                    "From " + SubTaskTable.tableName + " " +
                    "Where " + SubTaskTable.taskId + " = ? ;";
    /**
     * 获取指定任务的子任务
     */
    public ArrayList<SubTaskBean.DataBean> getSubTasks(Integer taskId){
        ArrayList<SubTaskBean.DataBean> result = new ArrayList<>();
        SQLiteDatabase database = getReadableDatabase();
        Cursor cursor = database.rawQuery(getSubTaskOfTaskSQL, new String[]{String.valueOf(taskId)});

        if(cursor.moveToFirst()){
            do{
                SubTaskBean.DataBean tempDataBean = new SubTaskBean.DataBean();

                tempDataBean.setTaskId(taskId);
                tempDataBean.setId(cursor.getInt(cursor.getColumnIndex(SubTaskTable.id)));
                tempDataBean.setTitle(cursor.getString(cursor.getColumnIndex(SubTaskTable.title)));
                tempDataBean.setIfDone(cursor.getString(cursor.getColumnIndex(SubTaskTable.ifDone)));

                result.add(tempDataBean);
            }while (cursor.moveToNext());
        }
        // 关闭游标和数据库
        cursor.close();
        database.close();
        logInfo("获取子任务：taskId = " + taskId + "data->"+ result.toString());
        return result;

    }
    /**
     * 获取所有未指定日期的任务
     */
    public ArrayList<TaskBean> getWaitingTask(Integer userId) {
        ArrayList<TaskBean> result = null;

        SQLiteDatabase database = getReadableDatabase();
        Cursor cursor = database.rawQuery(getWaitingTaskSQL, new String[]{String.valueOf(userId)});
        if (cursor.getCount() > 0) {
            result = new ArrayList<>();
            TaskBean temp;
            TaskBean.DataBean tempData;
            while (cursor.moveToNext()) {
                temp = new TaskBean();
                tempData = new TaskBean.DataBean();

                tempData.setId(cursor.getInt(0));
                tempData.setTitle(cursor.getString(1));
                tempData.setDescription(cursor.getString(2));
                tempData.setTime(cursor.getString(3));
                tempData.setRate(cursor.getString(4));
                tempData.setIfDone(cursor.getString(5));

                temp.setData(tempData);
                result.add(temp);
            }
        }

        cursor.close();
        database.close();

        logInfo("获取用户" + userId + "的所有待办任务");

        return result;
    }

    /**
     * 原意是设置待办日期，但是实际上也可以用来修改任务的日期
     */
    public boolean setDateOfTask(Integer taskId, Integer userId, String date) {
        SQLiteDatabase database = getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        contentValues.put(TaskTable.date, date);
        // 被更新的列数
        int affectRows = database.update(
                TaskTable.tableName,
                contentValues,
                TaskTable.id + " = " + taskId + " and " + TaskTable.userId + " = " + userId,
                null);

        recordOperation(database,userId,OperationTable.TASK_TYPE, taskId, OperationTable.CRUD_U);

        database.close();

        if (affectRows > 1) {
            // TODO: 想一想如何处理这种情况，因为理论上只会有1行数据受影响
            logError("设置任务日期时，应当仅修改一个任务，但是实际上却修改了" + affectRows + "个任务");
        }

        if (affectRows == 1) {
            logInfo("设置任务" + taskId + "的日期为" + date + "成功");
        }
        else if (affectRows > 1) {
            logError("设置任务" + taskId + "的日期为" + date + "时，修改了多行数据，有未知问题");
        }
        else {
            logError("未找到任务" + taskId + "，设置任务日期为" + date + "失败");
        }

        // 修改成功则应仅有一行被修改
        return affectRows == 1;
    }


    // 基于task id查询 task
    public TaskBean.DataBean getTaskById(int taskId){

        TaskBean.DataBean task = new TaskBean.DataBean();
        // 执行查询语句并尝试读取用户名
        SQLiteDatabase database = getReadableDatabase();


        Cursor cursor = database.query(TaskTable.tableName,null,
                TaskTable.id + "=?",new String[]{taskId+""},null,null,null);

        if (cursor.getCount()>0 && cursor.moveToFirst()) {
            task.setId(cursor.getInt(cursor.getColumnIndex(TaskTable.id)));
            task.setUserId(cursor.getInt(cursor.getColumnIndex(TaskTable.userId)));
            task.setTitle(cursor.getString(cursor.getColumnIndex(TaskTable.title)));
            task.setDescription(cursor.getString(cursor.getColumnIndex(TaskTable.description)));
            task.setDate(cursor.getString(cursor.getColumnIndex(TaskTable.date)));
            task.setTime(cursor.getString(cursor.getColumnIndex(TaskTable.time)));
            task.setRate(cursor.getString(cursor.getColumnIndex(TaskTable.rate)));
            task.setIfDone(cursor.getString(cursor.getColumnIndex(TaskTable.ifDone)));
        }

        // 关闭游标和数据库
        cursor.close();
        database.close();
        return task;
    }


    // 基于 subtask id查询 subtask
    public SubTaskBean.DataBean getSubTaskById(int subTaskId){
        SubTaskBean.DataBean subTask = new SubTaskBean.DataBean();
        // 执行查询语句并尝试读取用户名
        SQLiteDatabase database = getReadableDatabase();

        Cursor cursor = database.query(SubTaskTable.tableName,null,
                SubTaskTable.id + "=?",new String[]{subTaskId+""},null,null,null);


        if (cursor.getCount()>0 && cursor.moveToFirst()) {
            subTask.setId(cursor.getInt(cursor.getColumnIndex(SubTaskTable.id)));
            subTask.setTaskId(cursor.getInt(cursor.getColumnIndex(SubTaskTable.taskId)));
            subTask.setTitle(cursor.getString(cursor.getColumnIndex(SubTaskTable.title)));
            subTask.setIfDone(cursor.getString(cursor.getColumnIndex(SubTaskTable.ifDone)));
        }
        // 关闭游标和数据库
        cursor.close();
        database.close();
        return subTask;
    }

    /**
     * 删除userId指定用户的taskId的任务，删除成功后请调用如下方法更新相应的计数值
     *      {@link #updateNumWithValue(int, boolean, Integer, String)}
     */
    public void deleteTaskOf(Integer userId, TaskBean taskBean) {

        TaskBean.DataBean data = taskBean.getData();
        SQLiteDatabase database = getWritableDatabase();
        try {
            database.beginTransaction();
            database.delete(
                    TaskTable.tableName,
                    TaskTable.userId + " = " + userId + " and " + TaskTable.id + " = " + data.getId(),
                    null);
            // 记录操作
            recordOperation(database,userId,OperationTable.TASK_TYPE, data.getId(), OperationTable.CRUD_D);

            // 删除所有子任务
            deleteSubTaskOf(data.getId());
            // 记录操作
            ArrayList<SubTaskBean.DataBean > sub = data.getSubTasks();
            if(sub!=null){
                for(SubTaskBean.DataBean d :sub ){
                    recordOperation(database,userId,OperationTable.SUB_TASK_TYPE, d.getId(), OperationTable.CRUD_D);
                }
            }
            database.setTransactionSuccessful();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            database.endTransaction();
            database.close();
        }
    }

    /**
     * 删除某个任务所有子任务
     */
    public void deleteSubTaskOf(Integer taskId) {
        SQLiteDatabase database = getWritableDatabase();
        try {
            database.beginTransaction();
            database.delete(
                    SubTaskTable.tableName,
                    SubTaskTable.taskId + " = " + taskId ,
                    null);
            // todo: 在父操作记录操作
            database.setTransactionSuccessful();

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            database.endTransaction();
        }
    }

    /**
     * 设置任务状态
     */
    public void setTaskState(Integer taskId, Integer userId, boolean done) {
        SQLiteDatabase database = getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        if(done){
            contentValues.put(TaskTable.ifDone, "是");
        }else {
            contentValues.put(TaskTable.ifDone, "否");
        }
        try {
            database.beginTransaction();
            database.update(
                    TaskTable.tableName,
                    contentValues,
                    TaskTable.id + " = " + taskId + " and " + TaskTable.userId + " = " + userId,
                    null);

            // 更新子任务的完成状态
//            finishSubTaskOfTask(taskId);

            recordOperation(database,userId,OperationTable.TASK_TYPE, taskId, OperationTable.CRUD_U);

            database.setTransactionSuccessful();
        }catch (Exception e){
            e.printStackTrace();
        }finally {

            database.endTransaction();
        }
    }

    /**
     * 设置子任务状态
     */
    public void setSubTaskState(Integer subTaskId,int userId,boolean done) {
        SQLiteDatabase database = getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        if(done){
            contentValues.put(SubTaskTable.ifDone, "是");
        }else {
            contentValues.put(SubTaskTable.ifDone, "否");
        }
        try{
            database.beginTransaction();
            // 更新状态
            database.update(
                    SubTaskTable.tableName,
                    contentValues,
                    SubTaskTable.id + " = ?",
                    new String[]{String.valueOf(subTaskId)});
            // 记录操作
            recordOperation(database,userId,OperationTable.SUB_TASK_TYPE, subTaskId, OperationTable.CRUD_U);

            // todo:动态更新主任务
           // updateParentTaskOf(subTaskId);
            database.setTransactionSuccessful();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            database.endTransaction();
        }
    }

    /**
     * 用于在父任务完成时将所有子任务的状态也设置为已完成
     *
     * @param taskId 父任务的id
     */
    private void finishSubTaskOfTask(Integer taskId) {
        SQLiteDatabase database = getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        contentValues.put(SubTaskTable.ifDone, "是");
        database.update(
                SubTaskTable.tableName,
                contentValues,
                SubTaskTable.taskId + " = " + taskId,
                null);

    }

    /**
     * 基于子任务id查询父任务id
     *
     * @param subTaskId 被查询的子任务的id
     * @return 查询成功则返回正确的id，未查询到数据则返回-1
     */
    private int getParentTaskIdOf(Integer subTaskId) {
        SQLiteDatabase database = getReadableDatabase();

        int parentTaskId;
        Cursor cursor = database.query(
                SubTaskTable.tableName,
                new String[]{SubTaskTable.taskId},
                SubTaskTable.id + " = " + subTaskId,
                null,
                null,
                null,
                null
        );
        // 查询失败，理论上不会进入这个分支
        if (!cursor.moveToFirst()) {
            logError("基于子任务状态更新父任务状态时，获取子任务的父任务id失败");
            cursor.close();
            return - 1;
        }

        // 查询成功，则尝试更新父任务的状态
        parentTaskId = cursor.getInt(0);
        // 关闭查询父任务id的游标
        cursor.close();

        return parentTaskId;
    }

    /**
     * 基于父任务查询子任务的完成状态
     *
     * @param taskId 被查询的父任务的id
     * @return 返回一个查询到的子任务数据域列表，每一个数据项都只有id和if_done被赋值
     */
    private ArrayList<SubTaskBean.DataBean> getSubTaskStatesOf(Integer taskId) {
        ArrayList<SubTaskBean.DataBean> result = new ArrayList<>();

        SQLiteDatabase database = getReadableDatabase();

        // 2. 获取该父任务的所有子任务状态
        Cursor cursor = database.query(
                SubTaskTable.tableName,
                new String[]{SubTaskTable.id, SubTaskTable.ifDone},
                SubTaskTable.taskId + " = " + taskId,
                null,
                null,
                null,
                null
        );
        SubTaskBean.DataBean temp = null;
        while (cursor.moveToNext()) {
            temp = new SubTaskBean.DataBean();
            temp.setId(cursor.getInt(0));
            temp.setIfDone(cursor.getString(1));
            result.add(temp);
        }

        // 关闭数据库和游标
        cursor.close();

        return result;
    }

    /**
     * 基于任务id查询任务的拥有者id，
     * 仅在数据库内部使用，若要在外部使用，请修改访问限制
     *
     * @param taskId 被查询的任务id
     * @return 查询成功返回拥有者id，查询失败返回-1
     */
    private int getUserIdOf(Integer taskId) {
        SQLiteDatabase database = getReadableDatabase();

        // 3. 查询父任务的拥有者
        Cursor cursor = database.query(
                TaskTable.tableName,
                new String[]{TaskTable.userId},
                TaskTable.id + " = " + taskId,
                null,
                null,
                null,
                null
        );
        // 未查询到父任务的拥有者，理论上不会进入这个分支
        if (!cursor.moveToFirst()) {
            logError("基于子任务状态更新父任务状态时，基于父任务id查找任务拥有者时，未查询到用户，不符合预期情况");
            cursor.close();
            return -1;
        }

        int userId = cursor.getInt(0);
        // 关闭查询任务拥有者的游标
        cursor.close();

        return userId;
    }

    /**
     * 自动更新父任务
     */
//    private void updateParentTaskOf(Integer subTaskId) {
//        // 1. 获取该子任务的父任务id
//        int parentTaskId = getParentTaskIdOf(subTaskId);
//        // 查询失败直接返回
//        if (parentTaskId == -1) {
//            return;
//        }
//
//        // 2. 获取该父任务的所有子任务状态
//        ArrayList<SubTaskBean.DataBean> subTaskStates = getSubTaskStatesOf(parentTaskId);
//        // 这里查询出的子任务数至少为1，所以一定会进入循环
//        boolean needToFinishParent = true;
//        for (SubTaskBean.DataBean temp : subTaskStates) {
//            if (temp.getIfDone().equals("否")) {
//                needToFinishParent = false;
//                break;
//            }
//        }
//
//        // 若不需要更新父任务则直接返回
//        if (!needToFinishParent) {
//            logInfo("基于子任务状态更新父任务状态时，检测出不需要更新父任务的状态");
//            return;
//        }
//
//        // 3. 查询父任务的拥有者
//        int userId = getUserIdOf(parentTaskId);
//        // 查询失败直接返回
//        if (userId == -1) {
//            return;
//        }
//
//        // 4. 开始更新父任务的状态
//        boolean result = finishTaskOf(parentTaskId, userId);
//        if (!result) {
//            logError("基于子任务状态更新父任务状态时，更新失败");
//        }
//        else {
//            logInfo("基于子任务状态更新父任务状态时，更新成功");
//        }
//    }

    /**
     * 将用户缓存表中由 columnName 指定的列的值 增加 或 减少 value，只能对计数值列产生作用
     *
     * 这个实现麻烦了，这样实现主要是为了获取影响到的行数，进而判断是否修改成功
     *
     * @param value 要增加或减少的 值大小
     * @param increment true表示将指定列的值增加 value，false表示将指定列的值减少 value
     * @param userId 要被修改的用户id
     * @param columnName 用户缓存表中要修改的列名，只能是该类成员变量的成员之一
     *                   传入该参数时，请使用定义在{@link UserTable}中的字符串常量作为实参
     * @return 修改成功则返回true，输入列名不能被修改或修改失败返回false
     */
    public boolean updateNumWithValue(int value, boolean increment, Integer userId, final String columnName) {
        if (userCountColumnsName.contains(columnName)) {
            int oldValue = -1;

            // 1. 获取原始数据
            UserBean.DataBean userData = getUserData(userId).getData();
            switch (columnName) {
                case UserTable.historyTaskNum:
                    oldValue = userData.getHistoryTaskNum();
                    break;
                case UserTable.historyDoneTaskNum:
                    oldValue = userData.getHistoryDoneTaskNum();
                    break;
                case UserTable.todayTaskNum:
                    oldValue = userData.getTodayTaskNum();
                    break;
                case UserTable.todayDoneTaskNum:
                    oldValue = userData.getTodayDoneTaskNum();
                    break;
                case UserTable.waitTaskNum:
                    oldValue = userData.getWaitTaskNum();
                    break;
                default:
                    logError("更新用户表的数据时，尝试修改不能被修改的列的值");
                    break;
            }

            // 该分支理论上不会进入，若进入了该分支应当是由于待修改的列不存在而引发的
            if (oldValue == -1) {
                return false;
            }

            // 2. 将变化后的值写入数据库
            SQLiteDatabase database = getWritableDatabase();
            ContentValues contentValues = new ContentValues();
            // 按照调用者的期望将指定列的值 增加 或 减少 1
            contentValues.put(columnName, (increment ? oldValue + value : oldValue - value));
            int affectRows = database.update(
                    UserTable.tableName,
                    contentValues,
                    UserTable.id + " = " + userId,
                    null
            );

            // 关闭数据库

            if (affectRows > 1) {
                logError("更新用户表的数据时，修改了多行的数据，这是不应该发生的");
                return false;
            }
            else if (affectRows < 1) {
                logError("更新用户表的数据时，未修改任何行的数据");
                return false;
            }
            else {
                logInfo("更新用户表的数据时，成功修改了用户" + userId + ", " + columnName + "列的数据");
                return true;
            }
        }
        else {
            return false;
        }
    }


    /**
     * 添加操作记录
     */
    private boolean recordOperation(SQLiteDatabase db,int userId, String type, long typeId, String crud){

        // 记录用户操作
        ContentValues operations = new ContentValues();
        operations.put(OperationTable.USER_ID,userId);
        operations.put(OperationTable.TYPE,type);
        operations.put(OperationTable.TYPE_ID,typeId);
        operations.put(OperationTable.CRUD,crud);
        long res = db.insert(OperationTable.TABLE_NAME,null,operations);
        logError("recordOperation---》userId=" + userId +"  type="+ type +"  typeId"+ typeId +"type="+ type);
        return res != -1;
    }
    /**
     * 读取所有操作记录
     */
    public List<OperationBean> readAllOperation(Integer userId){

        SQLiteDatabase db = getReadableDatabase();
        // 记录用户操作
        List<OperationBean> operations = new ArrayList<>();

        Cursor cursor = db.query(OperationTable.TABLE_NAME,null,OperationTable.USER_ID+"=?",
                new String[]{userId+""},OperationTable.ID,null,null);

        if (cursor.moveToFirst()){
            do {
                OperationBean operation = new OperationBean();
                operation.setType(cursor.getString(cursor.getColumnIndex(OperationTable.TYPE)));
                operation.setTypeId(cursor.getInt(cursor.getColumnIndex(OperationTable.TYPE_ID)));
                operation.setCrud(cursor.getString(cursor.getColumnIndex(OperationTable.CRUD)));
                logError("..\nReadAllRecord-->userId=" + cursor.getInt(cursor.getColumnIndex(OperationTable.USER_ID)));
                operations.add(operation);
            }while (cursor.moveToNext());
        }

        cursor.close();
        logError("..\n..\nReadAllRecord\n" + operations.toString());
        return operations;
    }
    /**
     * 删除所有操作记录
     */
    public void deleteAllOperations(int userId){
        SQLiteDatabase db = getWritableDatabase();
        db.delete(OperationTable.TABLE_NAME,OperationTable.USER_ID + "=" + userId,null);
        logError("..\n..\ndeleteAllOperations\ndelete all operation!");
    }
}
